package cn.ainit.sys.ds.store;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import oracle.jdbc.OracleTypes;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ainit.butn.proceduce.CommonProcedure;
import cn.ainit.common.ReturnObject;
import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.framework.entity.SysColumns;
import cn.ainit.sys.framework.save.ReceiveObj;
import cn.ainit.sys.framework.save.ReceiveZM;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.utils.DateTimeStr;

@Transactional
@Repository("storeDao")
public class StoreDaoImpl extends SysBaseDao implements StoreDao {
	@Override
	public String findSql(String dsId) throws Exception {
		try {
			String hql = "select sqlText from DataStore ds where ds.dsId=?";
			Query query = this.getSessionFactory().getCurrentSession().createQuery(hql);
			query.setString(0, dsId);
			List list = query.list();
			if (list !=null && !list.equals("")) {
				return (String) query.list().get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	/***
	 * 根据sql返回单个的String类型
	 */
	@Override
	public String getStringBySql(String sql) throws Exception{
		String data=null;
		try {
			data = jdbcTemplate.queryForObject(sql,String.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;
	};



	@Override
	public DataStore findStore(String dsId) throws Exception {
		try {
			String hql = " from DataStore ds where ds.dsId = ?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, dsId);
			List<DataStore> list = query.list();
			if (list != null && list.size() > 0) {
				return list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public Object findObjects(String sql) throws Exception {
		return this.executeSQLRetObj(sql);
	}


	/**
	 *审核时的更新
	 * @throws Exception
	 * author:cff
	 * 2014/3/26 14:38
	 */
	@Override
	public Integer checkObject(String sql)
	throws Exception {
		Integer result=null;
		try{
			result=this.jdbcTemplate.update(sql);
		}catch (Exception e) {
			e.printStackTrace();
			throw new Exception("跟新时异常");
		}
		return result;
	}


	/**
	 *审核时根据主键得到对象的审核状态
	 * @throws Exception
	 * author:cff
	 * 2014/3/27 10:55
	 */
	@Override
	public Integer getIsVerify(String hql)throws Exception{
		try{
			Integer verify=this.jdbcTemplate.queryForInt(hql);
			return verify;
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}


	/***
	 * 作废时根据主键得到作废之前的状态
	 * @param hql
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer getIsDestory(String hql) throws Exception {
		try{
			Integer destroy=this.jdbcTemplate.queryForInt(hql);
			return destroy;
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public CommonProcedure procedureByDsId(String dsId,String code) throws Exception {
		try {
			String hql = "from CommonProcedure c where c.dsId = ? and c.code =?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0,dsId);
			query.setString(1,code);
			List<CommonProcedure> list = query.list();
			if(list != null && !list.isEmpty()){
				return list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public boolean updDestory(String dsId,String manId,String whereStr,String aord) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			String[] dsIds = dsId.split(";");
			String sql ="";
			String wheresql=whereStr;
			List<DataStore> list = new ArrayList<DataStore>();
			for (int i = 0; i < dsIds.length; i++) {
				DataStore dataStore =  findStore(dsIds[i]);
				list.add(dataStore);
			}
			if(aord.equals("0")){
				//作废    让destroy=1 作废   让verify=0 审核
				sql="update "+list.get(0).getUpdateTable()+" set "+list.get(0).getDestroy()+"='1',"+list.get(0).getVerify()+"='0',DESTROY_MAN='"+manId+"',DESTROY_DATE=?,LAST_MOD_DATE=?,LAST_MOD_MAN=? where "+wheresql;
				statement = conn.prepareCall(sql);
				statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()/1000*1000));
				statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()/1000*1000));
				statement.setString(3, manId);
			}else if(aord.equals("1")){
				//审核
				sql="update "+list.get(0).getUpdateTable()+" set "+list.get(0).getVerify()+"='1',VERIFY_MAN='"+manId+"',VERIFY_DATE=?,LAST_MOD_DATE=?,LAST_MOD_MAN=? where "+wheresql;
				statement = conn.prepareCall(sql);
				statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()/1000*1000));
				statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()/1000*1000));
				statement.setString(3, manId);
			}else if (aord.equals("2")) {
				//还原
				sql="update "+list.get(0).getUpdateTable()+" set "+list.get(0).getDestroy()+"='0' where "+wheresql;
				statement = conn.prepareCall(sql);
			}
			statement.executeUpdate();
			statement.execute("commit");
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			conn.rollback();
			conn.setAutoCommit(true);
			e.printStackTrace();
		}finally{
			if (conn!=null) {
				conn.close();
			}
			if (statement!= null) {
				statement.close();
			}
		}
		return false;
	}
	@Override
	public ReturnObject execProcedure(String djId,String dsId, String manId, String stationId,String proceName)
	throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			String sql = "{Call "+proceName+"(?,?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,djId);
			cs.setString(2,dsId);
			cs.setString(3,manId);
			cs.setString(4,stationId);
			cs.registerOutParameter(5,OracleTypes.VARCHAR);
			cs.execute();
			putValue = cs.getString(5);
			if(putValue!=null){
				int index=putValue.indexOf("@");
				if(index!=-1){
					putValue=putValue.substring(0,index);
				}
			}
			returnObject.setMsg("success");
		} catch (SQLException se) {
			returnObject.setMsg("error");
			String sbmsgs= se.getMessage();
			int index=sbmsgs.indexOf("@");
			if(index!=-1){
				sbmsgs=sbmsgs.substring(0,index);
			}
			String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
			if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
				String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
				//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
				String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
				cs = conn.prepareCall(errSql);  
				cs.registerOutParameter(1, Types.VARCHAR);  
				cs.setString(2,exproc);
				cs.setString(3,dsId);
				cs.execute();
				putValue= cs.getString(1);
			}else if(se.getErrorCode()== 20001){
				putValue=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
			}else{
				putValue=sbmsgs;
			}
			if(putValue!=null&&putValue.indexOf("@")!=-1){
				putValue=putValue.substring(0,putValue.indexOf("@"));
			}
		}catch (Exception e) {
			returnObject.setMsg("error");
			putValue = e.getMessage();
			e.printStackTrace();
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		returnObject.setMsgInfo(putValue);
		return returnObject;
	}
	@Override
	public String execParentNode(String managerId) throws Exception {
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			String sql = "{? = call PK_BJ_XT.uf_GetParentDjInfo(?)}";
			cs = conn.prepareCall(sql);
			cs.registerOutParameter(1, Types.VARCHAR);
			cs.setString(2,managerId);
			cs.execute();
			putValue = cs.getString(1);;
		} catch (Exception e) {
			putValue = e.getMessage();
			e.printStackTrace();
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	@Override
	public List<String> callBackSql(String sqlText) throws Exception {
		try {
			return this.callBackCol(sqlText);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public boolean saveQueryCol(List<String> colnList, String dsId)
	throws Exception {
		Connection conn=null;
		PreparedStatement pstmt = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String insertSql = "INSERT INTO SYS_COLUMNS(DS_ID,NAME,JAVA_PROPERTY_RECORDED) VALUES('"+dsId+"',?,?)";
			for (int i = 0; i < colnList.size(); i++) {
				pstmt=conn.prepareStatement(insertSql);
				pstmt.setString(1, colnList.get(i));
				pstmt.setString(2, colnList.get(i));
				pstmt.execute();
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if (conn !=null) {
				conn.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
		}
	}
	@Override
	public List execSql(String sql) throws Exception {
		List list = new ArrayList();
		try {
			list = this.getJdbcTemplate().queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public ReturnObject saveConfig(Map<String,Object> receiveMobj)
	throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ReturnObject returnObject = new ReturnObject();
		try {
			conn =  this.getJdbcTemplate().getDataSource().getConnection();
			List<String> colnName = (List<String>) receiveMobj.get("colnNameList"); //所有修改过的columnName
			List<Map<String,Object>> mapRecords = (List<Map<String, Object>>) receiveMobj.get("mapRecords");//所有修改过的数据行
			String dsId = "";
			if (mapRecords != null && mapRecords.size() > 0) {
				Map<String,Object> mxObj = null;
				Map<String,Object> rowData = null;
				for (int i = 0; i < mapRecords.size(); i++) {
					StringBuffer sql = new StringBuffer("UPDATE SYS_COLUMNS SET ");
					mxObj = mapRecords.get(i); //每一行数据的map集合
					dsId = mxObj.get("dsId").toString();
					rowData = (Map<String, Object>) mxObj.get("mapRecord"); 
					Object[] updColnName  = rowData.keySet().toArray();
					List<String> valueList = new ArrayList<String>();
					for(int j=0;j<updColnName.length;j++){
						if ("DS_ID".equals(updColnName[j].toString()) || "NAME".equals(updColnName[j].toString())) {

						}else{
							sql.append(updColnName[j].toString()+"=?,");
							valueList.add(rowData.get(updColnName[j])==null?"":rowData.get(updColnName[j]).toString());
						}
					}
					sql = new StringBuffer(sql.substring(0,sql.length()-1));
					sql.append(" WHERE DS_ID ='"+dsId+"' AND NAME=?");
					pstmt = conn.prepareStatement(sql.toString());
					for (int k = 0; k < valueList.size(); k++) {
						pstmt.setString(k+1, valueList.get(k));
					}
					pstmt.setString((valueList.size()+1), colnName.get(i));
					pstmt.execute();
				}
			}
			returnObject.setMsg("success");
		} catch (Exception e) {
			returnObject.setMsg("fail");
			e.printStackTrace();
		}finally{
			if(pstmt != null){
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return returnObject;
	}
}
